/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package frame;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import library.DB;
import library.Debug;
import library.Utils;

/**
 *
 * @author Thien
 */
public class QuestionModel {
    private int questionID, correctAnswer, catID;
    private String question;
    private String[] Answers;
    private int[] AnswersID;

    public QuestionModel() {
        this.questionID = 0;
    }

    public int[] getAnswersID() {
        return AnswersID;
    }

    public QuestionModel(int CatID, String Question, String[] Answers, int Correct) {
        this.questionID = 0;
        this.catID = CatID;
        this.question = Question;
        this.Answers = Answers;
        this.correctAnswer = Correct;
    }

    public QuestionModel(int QID, int CatID, String Question, String[] Answers, int Correct) {
        this.questionID = QID;
        this.catID = CatID;
        this.question = Question;
        this.Answers = Answers;
        this.correctAnswer = Correct;
    }

    public QuestionModel(int QID, int CatID, String Question, String[] Answers, int[] AnswersID, int Correct) {
        this(QID, CatID, Question, Answers, Correct);
        this.AnswersID = AnswersID;

    }

    public String[] getAnswers() {
        return Answers;
    }

    public int getCatID() {
        return catID;
    }

    public int getCorrectAnswer() {
        return correctAnswer;
    }

    public String getQuestion() {
        return question;
    }

    public int getQuestionID() {
        return questionID;
    }

    public void setAnswers(String[] Answers) {
        this.Answers = Answers;
    }

    public void setAnswersID(int[] AnswersID) {
        this.AnswersID = AnswersID;
    }

    public void setCatID(int catID) {
        this.catID = catID;
    }

    public void setCorrectAnswer(int correctAnswer) {
        this.correctAnswer = correctAnswer;
    }

    public void setQuestion(String question) {
        this.question = question;
    }

    public void setQuestionID(int questionID) {
        this.questionID = questionID;
    }

    public int save() {
        int result = 0;
        // Edit
        if (questionID > 0) {
            try {
                DB.getConnection().setAutoCommit(false);
                PreparedStatement sm = DB.getConnection().prepareStatement("UPDATE Questions SET CatID = ?, Name = ? WHERE QuestionID = ?");
                sm.setInt(1, catID);
                sm.setString(2, question);
                sm.setInt(3, questionID);
                result = sm.executeUpdate();
                sm.close();
                PreparedStatement pm = DB.getConnection().prepareStatement("Update Answers SET Contents = ?, IsCorrect = ? WHERE AnswerID = ?");
                for (int i = 0; i < Answers.length; i++) {
                    String string = Answers[i];
                    int AID = AnswersID[i];
                    pm.clearParameters();
                    pm.setString(1, string);
                    pm.setBoolean(2, i == correctAnswer);
                    pm.setInt(3, AID);
                    result += pm.executeUpdate();
                }
                pm.close();
                DB.getConnection().commit();
                DB.getConnection().setAutoCommit(true);
            }
            catch (SQLException ex) {
                try {
                    DB.getConnection().rollback();
                    DB.getConnection().setAutoCommit(true);
                }
                catch (SQLException ex1) {
                    Logger.getLogger(QuestionModel.class.getName()).log(Level.SEVERE, null, ex1);
                }
                Logger.getLogger(QuestionModel.class.getName()).log(Level.SEVERE, null, ex);
                Utils.Alert(ex.getMessage());
                return -1;
            }
        }
        // Add new
        else {
            try {
                DB.getConnection().setAutoCommit(false);
                PreparedStatement sm = DB.getConnection().prepareStatement("INSERT INTO Questions (CatID, Name) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);
                sm.setInt(1, catID);
                sm.setString(2, question);
                result = sm.executeUpdate();
                ResultSet rs = sm.getGeneratedKeys();
                rs.next();

                int newQuestionID = rs.getInt(1);
                rs.close();
                sm.close();

                PreparedStatement pm = DB.getConnection().prepareStatement("INSERT INTO Answers (QuestionID, Contents, IsCorrect) VALUES (?, ?, ?)");
                for (int i = 0; i < Answers.length; i++) {
                    String string = Answers[i];
                    pm.clearParameters();
                    pm.setInt(1, newQuestionID);
                    pm.setString(2, string);
                    pm.setBoolean(3, i == correctAnswer);
                    result += pm.executeUpdate();
                }
                DB.getConnection().commit();
                DB.getConnection().setAutoCommit(true);
            }
            catch (SQLException ex) {
                try {
                    DB.getConnection().rollback();
                    DB.getConnection().setAutoCommit(true);
                }
                catch (SQLException ex1) {
                    Logger.getLogger(QuestionModel.class.getName()).log(Level.SEVERE, null, ex1);
                }
                Logger.getLogger(QuestionModel.class.getName()).log(Level.SEVERE, null, ex);
                Utils.Alert(ex.getMessage());
                return -1;
            }
        }

        Debug.Log(result);
        return result;
    }

    public static boolean removeQuestion(int ID) {
        boolean result = false;
        try {
            int counter = 0;
            DB.getConnection().setAutoCommit(false);
            counter += DB.execUpdate("DELETE FROM Answers WHERE QuestionID = " + ID);
            counter += DB.execUpdate("DELETE FROM Questions WHERE QuestionID = " + ID);
            DB.getConnection().commit();
            DB.getConnection().setAutoCommit(true);
            result = (counter == 5);
        }
        catch (SQLException ex) {
            try {
                DB.getConnection().rollback();
                DB.getConnection().setAutoCommit(true);
            }
            catch (SQLException ex1) {
                Logger.getLogger(QuestionModel.class.getName()).log(Level.SEVERE, null, ex1);
            }

            Logger.getLogger(QuestionModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
}
